excel cells formula How to Remove Specific Text from Cell in Excel (Easiest 11 Ways)

The following image shows an overview of removing specific text from cells in Excel.

Overview of Removing Specific Text from Cell

We have a dataset with 3 columns. We will remove specific string values from these cells based on various criteria.

Sample dataset of removing specific text from cell

Method 1 – Using the Find and Replace Option to Remove a Specific Text from Cells in Excel

We will remove the ending “-XYZ” string from Product Code cells.

Selection of column to apply find and replace feature

Steps:

Select the data tableGo to the Home tab and select Editing.Choose Find & Select and click Find.

Performing find and replace feature to remove specific character

The Find and Replace dialog box will pop up.Write -XYZ in Find What.Keep Replace blank.Select Replace All.

Putting specific characters to replace from the selected cells

You’ll get a notification about the number of replacements. Click OK.

Confirmation of replacement of characters from multiple cells

Here’s the result.

Final output using the find and replace feature

Read More: How to Remove Letters from Cell in Excel

Method 2 – Removing Text from Cell with Flash Fill

We will extract the product name before the dash and extract it in a separate Items column.

Sample dataset to remove characters using the flash fill feature

Steps:

Write down the part of the text you want to keep in Cell E5.

Typing manually to apply flash fill feature

Press Enter.

Choosing cells to perform flash fill option

Go to the Home tab.Select Editing, then Fill, and choose Flash Fill.

Clicking flash fill option from editing feature

Here’s the output.

Final output removing specific text from cell

Read More: How to Remove Text from an Excel Cell but Leave Numbers

Method 3 – Using the SUBSTITUTE Function to Remove Specific Text from Cells

We’ll remove the “-XYZ” part from Product Code cells and extract the rest in the Items column.

Sample dataset to apply SUBSTITUTE function to remove specific text from cell

Steps:

Select Cell E5.Insert the following formula=SUBSTITUTE(B5,"-XYZ","")

Applying SUBSTITUTE formula to substitute specific text from a cell

Press Enter.Drag down the Fill Handle tool.

Result by substituting extra characters from a cell

Here’s the result.

Final output using the SUBSTITUTE formula removing specific text

Method 4 – Applying the MID Function to Delete Specific Text from Cells

We’ll get the product names from the product code cells and put them in Items.

Sample dataset for removing specific characters applying the MID formula

Steps:

Select Cell E5.Insert the following formula.=MID(B5,1,FIND("-",B5,1)-1)

The FIND function finds the first dash in the B5 cell and returns its location. The MID function extracts the characters from the 1st character to that location from B5.

Performing the MID function to remove characters

Press Enter.Drag down the Fill Handle tool.

Result of removing characters from a column using the MID formula

You will get your desired result in the Items column.

Final output applying the MID formula removing characters from a column

Method 5 – Inserting the RIGHT Function to Remove Specific Text from Cells

In the Color with Code column, we have some colors combined with their code number. We’ll remove the code and the dash.

Sample dataset to apply RIGHT function for removing characters

Steps:

Select Cell E5.Use the following formula.=RIGHT(D5,LEN(D5)-FIND("-",D5,1))

D5 is the text,LEN(D5) is the total length of the stringFIND("-", D5,1) will give the position of character “-” and then the value will be deducted from the total length of the string and it will be the number of characters for the RIGHT function.

Formula of RIGHT to remove specific character

Press Enter.Drag down the Fill Handle tool.

Pulling the fill handle down to fill all the cells with the RIGHT function

You will get only the names of the colors as below.

Final output by removing specific characters by using RIGHT function

Read More: How to Remove Text before a Space with Excel Formula

Method 6 – Applying the LEFT Function to Remove Text from Cells

We want to extract the Color Codes.

Sample dataset to remove characters using the LEFT function

Steps:

Select Cell E5.Insert the following formula.=LEFT(D5,3)

D5 is the text,3 is the number of characters you want to extract.

Formula of LEFT to remove specific characters

Press Enter.Drag down the Fill Handle tool.

Dragging the fill handle to fill the column

You will get the codes of colors in the Color Code column.

Final output using the LEFT function

Method 7 – Using the REPLACE Function to Remove Text from Cells

We’ll remove the color codes from the color section and insert the results in the added the Color column.

Sample dataset to delete texts or numeric values

Steps:

Select Cell E5.Insert the following formula:=REPLACE(D5,1,4,"")

D5 is the text,1 is the start number, 4 is the number of characters you want to replace with blank.

Formula of REPLACE to delete specific characters from a cell

Press Enter.Drag down the Fill Handle tool.

Dragging the fill handle to apply the REPLACE function for other cells

You will get the names of colors in the Color column.

Final output using the REPLACE formula

Method 8 – Removing Text After a Specific Character in Excel with Find and Replace

We want to remove the last three sizes in the Size column.

Sample dataset to remove text after specific character

Steps:

Select the data table.Go to Home, then to Editing, choose Find & Select, and click on Replace.

Pressing keyboard shortcut to open find and replace window

The Find and Replace dialog box will appear.Write “,*” in the Find What box.Keep the Replace with box empty.Select Replace All.

Providing specific text to replace

Click OK on the notification.

Confirming replacement of specific texts

Click on Close and you can see the results.

Final output removing text after specific character

Read More: How to Remove Text After Character in Excel

Method 9 – Removing Multiple Characters Simultaneously with SUBSTITUTE

We want to remove all of the brackets separating colors in the Color with Code column and use “-” as a separator.

Sample dataset for removing multiple characters

Steps:

Select Cell E5.Insert the following formula:=SUBSTITUTE(SUBSTITUTE(D5,"(","-"),")","")

D5 is the text,SUBSTITUTE(D5,"(","-") here,“(” is the old text you want to replace with “-“.This output will be used by another SUBSTITUTE function.

Peroforming SUBSTITUTE formula for removing multiple characters

Press Enter.Drag down the Fill Handle tool.

Output removing multiple characters using the SUBSTITUTE formula

You will get your desired format in the output column as below.

Final output removing multiple characters

Method 10 – Deleting Texts before the N-th Occurrence of a Specific Character with RIGHT and SUBSTITUTE

We want to get only the last size instead of the 4 sizes in the Size column.

Saqmple dataset for deleting text before Nth occurance

Steps:

Select Cell E5.Insert the following formula:=RIGHT(SUBSTITUTE(C5,",",CHAR(9),3),LEN(C5)-FIND(CHAR(9),SUBSTITUTE(C5,",",CHAR(9),3),1)+1)

C5 is the text,SUBSTITUTE(C5,",", CHAR(9),3) here comma will be replaced by CHAR(9)(blank) and 3 is used to define the position of a comma before which I want to remove textsThe RIGHT function will give the output as the last size number from the right side.

Combination of RIGHT, SUBSTITUTE, CHAR, LEN, FIND functions to delete text before Nth occurance

Press Enter.Drag down the Fill Handle tool.

Output using the combination of RIGHT, SUBSTITUTE, CHAR, LEN, FIND functions

Here are the results.

Final output by deleting text before Nth occurance

Read More: How to Remove Everything After a Character in Excel

Method 11 – Removing Texts after the Nth Occurrence of a Specific Character Using LEFT and SUBSTITUTE

We’ll get only the first size.

Sample dataset for deleting text after Nth occurance

Steps

Select Cell E5.Insert the following formula:=LEFT(SUBSTITUTE(C5,",",CHAR(9),1),FIND(CHAR(9),SUBSTITUTE(C5,",",CHAR(9),1),1)-1)

C5 is the text,SUBSTITUTE(C5,",", CHAR(9),3) here comma will be replaced by CHAR(9)(blank) and 1 is used to define the position of a comma after which I want to remove textsThe LEFT function will give the output as the last size number from the left side.

Combination of LEFT, SUBSTITUTE, CHAR, FIND formula to delete text after Nth occurance

Press Enter.Drag down the Fill Handle tool.

Result of applied formula deleting text after Nth occurance

Here’s the result.

Final output by deleting text after Nth occurance

Read More: How to Remove Text between Two Characters in Excel

Download the Excel Workbook

Remove Specific Text.xlsx

SaveSavedRemoved 1

Tags: Excel Remove Text

Tanjima Hossainclass="mb10">

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts Leave a reply Cancel reply